var conn = require('../javascripts/connect');
var calculation1 = {};
calculation1.cal = async function (depYM) {//2级巴发展事业部的计算
    let needCal = [];
    //去年同期实际金额计算
    needCal[0] = `CALL Calculation('${depYM}','去年同期抓取')`;
    needCal[1] = `CALL Calculation('${depYM}','去年同期调整')`;
    needCal[2] = `UPDATE ${depYM} SET 去年同期调整=0 WHERE 行号>143`;
    needCal[3] = `CALL colSUM('${depYM}','去年同期实际值','去年同期抓取','去年同期调整')`;
    needCal[4] = `CALL Calculation('${depYM}','去年同期实际值')`;
    needCal[5] = `CALL RatioEva('${depYM}','去年同期比率','去年同期实际值')`;
    needCal[6] = `UPDATE ${depYM} SET 去年同期比率=0 WHERE 行号>143`;
    //去年同期累计金额计算
    let [depYM0, year, month] = depYM.split("_");
    month = Number(month);
    year = Number(year);
    depYM1 = `中化泉州项目部_${year}_${month}`;
    depYM2 = `大唐阜新项目部_${year}_${month}`;
    depYM3 = `中科湛江项目部_${year}_${month}`;
    depYM4 = `淮南项目部_${year}_${month}`;
    depYM5 = `武汉项目部_${year}_${month}`;
    depYM6 = `武汉合资项目部_${year}_${month}`;
    depYM7 = `宁夏同程_${year}_${month}`;
    depYM8 = `商检_${year}_${month}`;
    depYM9 = `同程配送_${year}_${month}`;
    depYM10 = `同程管理_${year}_${month}`;
    depYM11 = `同程仓储_${year}_${month}`;
    depYM12 = `包头项目部_${year}_${month}`;
    depYM13 = `伊犁项目部_${year}_${month}`;
    depYM14 = `其它项目部_${year}_${month}`;
    let depFiled = "去年同期累计";
    // colRecount('淮南项目部_2018_7','淮南项目部_2018_6','去年同期累计','去年同期实际值')
    needCal[7] = `UPDATE ${depYM} FZ,(SELECT A.行号,A.${depFiled} A,B.${depFiled} B,C.${depFiled} C,D.${depFiled} D,E.${depFiled} E,F.${depFiled} F,G.${depFiled} G,H.${depFiled} H,I.${depFiled} I,J.${depFiled} J,K.${depFiled} K,L.${depFiled} L,M.${depFiled} M,N.${depFiled} N FROM ${depYM1} A LEFT JOIN ${depYM2} B ON A.行号=B.行号 LEFT JOIN ${depYM3} C ON A.行号=C.行号 LEFT JOIN ${depYM4} D ON A.行号=D.行号 LEFT JOIN ${depYM5} E ON A.行号=E.行号 LEFT JOIN ${depYM6} F ON A.行号=F.行号 LEFT JOIN ${depYM7} G ON A.行号=G.行号 LEFT JOIN ${depYM8} H ON A.行号=H.行号 LEFT JOIN ${depYM9} I ON A.行号=I.行号 LEFT JOIN ${depYM10} J ON A.行号=J.行号 LEFT JOIN ${depYM11} K ON A.行号=K.行号 LEFT JOIN ${depYM12} L ON A.行号=L.行号 LEFT JOIN ${depYM13} M ON A.行号=M.行号 LEFT JOIN ${depYM14} N ON A.行号=N.行号) YTH SET FZ.${depFiled}=YTH.A+YTH.B+YTH.C+YTH.D+YTH.E+YTH.F+YTH.G+YTH.H+YTH.I+YTH.J+YTH.K+YTH.L+YTH.M+YTH.N,FZ.年份=${year},FZ.月份=${month},FZ.修改日期=now()  WHERE FZ.行号=YTH.行号`;
    needCal[8] = `UPDATE ${depYM} SET 去年同期累计=去年同期累计`;
    needCal[9] = `CALL Calculation('${depYM}','去年同期累计')`;
    needCal[10] = `UPDATE ${depYM} SET ${depFiled}=(SELECT IF(d.${depFiled}=0, d.${depFiled},t.${depFiled} / d.${depFiled}/4) FROM (SELECT * FROM ${depYM} WHERE 行号=62) t,(SELECT * FROM ${depYM} WHERE 行号=143) d) WHERE 行号=144`;
    needCal[11] = `CALL RatioEva('${depYM}','去年同期累计比率','去年同期累计')`;
    needCal[12] = `UPDATE ${depYM} SET 去年同期累计比率=0 WHERE 行号>143`;
    //年度计算金额计算
    needCal[13] = `CALL Calculation('${depYM}','年度计划金额')`;
    needCal[14] = `UPDATE ${depYM} SET ${depFiled}=(SELECT IF(d.${depFiled}=0, d.${depFiled},t.${depFiled} / d.${depFiled}/4) FROM (SELECT * FROM ${depYM} WHERE 行号=62) t,(SELECT * FROM ${depYM} WHERE 行号=143) d) WHERE 行号=144`;
    needCal[15] = `CALL RatioEva('${depYM}','年度计划金额比率','年度计划金额')`;
    needCal[16] = `UPDATE ${depYM} SET 年度计划金额比率=0 WHERE 行号>143`;
    //上月实际金额计算
    needCal[17] = `CALL Calculation('${depYM}','上月抓取值')`;
    needCal[18] = `CALL Calculation('${depYM}','上月调整值')`;
    needCal[19] = `UPDATE ${depYM} SET 上月调整值=0 WHERE 行号>143`;
    needCal[20] = `CALL colSUM('${depYM}','上月实际值','上月抓取值','上月调整值')`;
    needCal[21] = `CALL Calculation('${depYM}','上月实际值')`;
    needCal[22] = `CALL RatioEva('${depYM}','上月比率','上月实际值')`;
    needCal[23] = `UPDATE ${depYM} SET 上月比率=0 WHERE 行号>143`;
    //当月计划金额计算
    needCal[24] = `CALL Calculation('${depYM}','当月计划值')`;
    needCal[25] = `CALL RatioEva('${depYM}','当月计划比率','当月计划值')`;
    needCal[26] = `UPDATE ${depYM} SET 当月计划比率=0 WHERE 行号>143`;
    //当月实金额计算
    needCal[27] = `CALL Calculation('${depYM}','当月抓取值')`;
    needCal[28] = `CALL Calculation('${depYM}','当月调整值')`;
    needCal[29] = `UPDATE ${depYM} SET 当月调整值=0 WHERE 行号>143`;
    needCal[30] = `CALL colSUM('${depYM}','当月实际值','当月抓取值','当月调整值')`;
    needCal[31] = `CALL Calculation('${depYM}','当月实际值')`;
    needCal[32] = `CALL RatioEva('${depYM}','当月比率值','当月实际值')`;
    needCal[33] = `UPDATE ${depYM} SET 当月比率值=0 WHERE 行号>143`;
    needCal[34] = `CALL colDivision('${depYM}','当月实际完成率','当月实际值','当月计划值')`;
    needCal[35] = `UPDATE ${depYM} SET 当月实际完成率=0 WHERE 行号>143`;
    //当月累计金额计算
    depFiled = "当月累计金额";
    // colRecount('淮南项目部_2018_7','淮南项目部_2018_6','去年同期累计','去年同期实际值')
    needCal[36] = `UPDATE ${depYM} FZ,(SELECT A.行号,A.${depFiled} A,B.${depFiled} B,C.${depFiled} C,D.${depFiled} D,E.${depFiled} E,F.${depFiled} F,G.${depFiled} G,H.${depFiled} H,I.${depFiled} I,J.${depFiled} J,K.${depFiled} K,L.${depFiled} L,M.${depFiled} M,N.${depFiled} N FROM ${depYM1} A LEFT JOIN ${depYM2} B ON A.行号=B.行号 LEFT JOIN ${depYM3} C ON A.行号=C.行号 LEFT JOIN ${depYM4} D ON A.行号=D.行号 LEFT JOIN ${depYM5} E ON A.行号=E.行号 LEFT JOIN ${depYM6} F ON A.行号=F.行号 LEFT JOIN ${depYM7} G ON A.行号=G.行号 LEFT JOIN ${depYM8} H ON A.行号=H.行号 LEFT JOIN ${depYM9} I ON A.行号=I.行号 LEFT JOIN ${depYM10} J ON A.行号=J.行号 LEFT JOIN ${depYM11} K ON A.行号=K.行号 LEFT JOIN ${depYM12} L ON A.行号=L.行号 LEFT JOIN ${depYM13} M ON A.行号=M.行号 LEFT JOIN ${depYM14} N ON A.行号=N.行号) YTH SET FZ.${depFiled}=YTH.A+YTH.B+YTH.C+YTH.D+YTH.E+YTH.F+YTH.G+YTH.H+YTH.I+YTH.J+YTH.K+YTH.L+YTH.M+YTH.N,FZ.年份=${year},FZ.月份=${month},FZ.修改日期=now()  WHERE FZ.行号=YTH.行号`;
    needCal[37] = `UPDATE ${depYM} SET 当月累计金额=当月累计金额`;
    needCal[38] = `CALL Calculation('${depYM}','当月累计金额')`;
    needCal[39] = `UPDATE ${depYM} SET ${depFiled}=(SELECT IF(d.${depFiled}=0, d.${depFiled},t.${depFiled} / d.${depFiled}/4) FROM (SELECT * FROM ${depYM} WHERE 行号=62) t,(SELECT * FROM ${depYM} WHERE 行号=143) d) WHERE 行号=144`;
    needCal[40] = `CALL RatioEva('${depYM}','当月累计比率','当月累计金额')`;
    needCal[41] = `UPDATE ${depYM} SET 当月累计比率=0 WHERE 行号>143`;
    needCal[42] = `CALL colDivision('${depYM}','当月累计完成率','当月累计金额','年度计划金额')`;
    needCal[43] = `UPDATE ${depYM} SET 当月累计完成率=0 WHERE 行号>143`;
    //累比增减额
    needCal[44] = `CALL colReduce('${depYM}','累比增减额','当月累计金额','去年同期累计')`;
    needCal[45] = `CALL Calculation('${depYM}','累比增减额')`;
    needCal[46] = `UPDATE ${depYM} SET 累比增减额=当月累计金额-去年同期累计 WHERE 行号>143`;
    //累比增减率
    needCal[47] = `CALL colDivision('${depYM}','累比增减率','累比增减额','去年同期累计')`;
    needCal[48] = `UPDATE ${depYM} SET 累比增减率=0 WHERE 行号>143`;
    //环比
    needCal[49] = `CALL colReduce('${depYM}','环比金额','当月实际值','上月实际值')`;
    needCal[50] = `CALL colDivision('${depYM}','环比增降幅','环比金额','上月实际值')`;
    needCal[51] = `UPDATE ${depYM} SET 环比增降幅=0 WHERE 行号>143`;
    //同比
    needCal[52] = `CALL colReduce('${depYM}','同比金额','当月实际值','去年同期实际值')`;
    needCal[53] = `CALL colDivision('${depYM}','同比增降幅','同比金额','去年同期实际值')`;
    needCal[54] = `UPDATE ${depYM} SET 同比增降幅=0 WHERE 行号>143`;

    //计划比
    needCal[55] = `CALL colReduce('${depYM}','计划比金额','当月实际值','当月计划值')`;
    needCal[56] = `CALL colDivision('${depYM}','计划增降幅','计划比金额','当月计划值')`;
    needCal[57] = `UPDATE ${depYM} SET 计划增降幅=0 WHERE 行号>143`;
    //下月计划
    needCal[58] = `CALL Calculation('${depYM}','下月计划值')`;
    needCal[59] = `CALL RatioEva('${depYM}','下月比率值','下月计划值')`;
    needCal[60] = `UPDATE ${depYM} SET 下月比率值=0 WHERE 行号>143`;

    for (let i = 0; i < needCal.length; i++) {
        await (async function (i) {
            await new Promise((res1, rej1) => {
                console.log("needCal[i]", i, needCal[i]);
                conn.connection2.query(needCal[i], function (err, result, files) {
                    if (err) {
                        console.log('[ERROR UPDATE]', err.message);
                        return;
                    }
                    console.log("计算成功", result);
                    res1(true);
                })
            })
        })(i)
    }
    return true;
}
module.exports = {
    calculation1
};
